from shoeKeyWords import *
from notneeded import *
from spokeMen import *
from brands import *

import xlrd
import math
import re

# 获取订单明细列表
def getOrderList(path, t):
    print('准备读取订单数据')

    ordersDict = {}

    orderData = xlrd.open_workbook(path)
    orderTable = orderData.sheet_by_index(0)

    for row in range(1, orderTable.nrows):
        title = orderTable.cell_value(row, 19)
        reason = orderTable.cell_value(row, 27)

        if not hasCommonModelNum(title, t):
            continue

        # 以orderId为key，以orderTime，actualPay为被映射的对象
        orderId, orderTime, actualPay = orderTable.cell_value(row, 0), orderTable.cell_value(row, 17), orderTable.cell_value(row, 8)
        ordersDict[orderId] = {
            "orderTime": xlrd.xldate.xldate_as_datetime(orderTime, orderData.datemode),
            "actualPay": actualPay,
            "reason": reason
        }

    return ordersDict

# 清洗订单数据，形成一个规范的、可供分析数据集,ExportOrderDetailList.xlsx
def getDataList(table, nrows, ncols, orderDict, t):
    print('订单数据读取完毕，准备生成订单的二维数据')

    datalist = []

    for row in range(1, nrows):
        line = []

        id = table.cell_value(row, 0)
        title = table.cell_value(row, 1)
        state = table.cell_value(row, 8)

        if not hasCommonModelNum(title, t):
            continue

        if not id in orderDict:
            continue

        if state == '未创建支付宝交易' or state == '等待买家付款':
            continue

        # 交易关闭的原因有好几种，只有原因为退款的交易关闭的宝贝参与运算
        if state == '交易关闭' and orderDict[id]['reason'] != '退款':
            continue

        # 数字标题废弃
        if isinstance(title, float) or isinstance(title, int):
            continue

        # 空标题废弃
        if len(title) < 3:
            continue

        # 运费和邮费补差价废弃
        if title.find('运费') != -1 or title.find('邮费') != -1:
            continue

        # 如果是不需要的运算的宝贝则跳过
        if isNotNeeded(title):
            continue

        # 把每一个属性都存入数组
        for k in range(ncols):
            line.append(table.cell_value(row, k))

        # 品牌
        brand = getBrand(title)
        if brand != None:
            line.append(brand)
        else:
            line.append(None)

        # 代言人
        spokeMan = getSpokeMan(title)
        if spokeMan != None:
            line.append(spokeMan)
        else:
            line.append(None)

        # 宝贝品类
        category = getCategory(title)
        if category != None:
            line.append(category)
        else:
            line.append(None)

        if category == None:
            continue

        # 实际支付金额
        if id in orderDict:
            line.append(orderDict[id]["actualPay"])
        else:
            print(title, 'has no actualPay')
            line.append(None)

        # 订单时间
        if id in orderDict:
            line.append(orderDict[id]["orderTime"])
        else:
            line.append(None)

        if orderDict[id]["orderTime"] == None:
            continue

        datalist.append(line)

    print('实际有效数据{0}行'.format(len(datalist)))
    return datalist

# 汇总宝贝数据
def summaryProducts(datalist, t):
    print('订单二维数据生成完毕，准备汇总数据,所需较长时间，请您耐心等待...')

    productsSummary = {}

    for row in range(0, len(datalist)):
        matched = False

        title = datalist[row][1]

        if not hasCommonModelNum(t, title):
            continue

        count = int(datalist[row][3])
        price = float(datalist[row][2])

        for k in productsSummary:
            # 如果是完全相同的标题或者是有至少一个共同的款号同时有相同的品牌和品类，则认为这两个宝贝是同一个宝贝
            if (title == k) or hasCommonModelNum(title, k):
                productsSummary[k]['saleAmt'] += count
                productsSummary[k]['saleMoneny'] += count * price
                productsSummary[k]['data'].append(datalist[row])

                matched = True
                break

        if not matched:
            productsSummary[title] = {
                'data': [],
                'saleAmt': 0,
                'saleMoneny': 0,
                'brand': getBrand(title),
                'category': getCategory(title),
                'spkoeMan': getSpokeMan(title)
            }

            productsSummary[title]['saleAmt'] += count
            productsSummary[title]['saleMoneny'] += count * price
            productsSummary[title]['data'].append(datalist[row])

    return productsSummary

# 搜索某款宝贝
def getProduct(productsSummary, title):
    for t in productsSummary:
        if hasCommonModelNum(title, t):
            return productsSummary[t]["data"]

    return None

# 获取类目
def getCategory(title):
    for k in runningShoeKeyWords:
        if title.find(k) != -1:
            return '跑步鞋'

    for k in casualShoeKeyWords:
        if title.find(k) != -1:
            return '休闲鞋'

    for k in baskballShoeKeyWords:
        if isinstance(k, list):
            flag = True
            for n in k:
                if title.find(n) == -1:
                    flag = False
            if flag:
                return '篮球鞋'
        elif title.find(k) != -1:
            return '篮球鞋'

    for k in skateShoeKeyWords:
        if title.find(k) != -1:
            return '板鞋'

    return None

# 获取品牌
def getBrand(title):
    for brand in brands:
        if title.find(brand) != -1:
            return brands[brand]

    return None

# 获取代言明星
def getSpokeMan(title):
    for i in range(len(spokeMen)):
        if title.find(spokeMen[i]) != -1:
            if spokeMen[i] in spokeManDict:
                return spokeManDict[spokeMen[i]]
            return spokeMen[i]

    return None

# 判断是否是不需要运算的宝贝
def isNotNeeded(title):
    for i in range(len(notneeded)):
        if title.find(notneeded[i]) != -1:
            return True

    return False

# 获取宝贝所有款号
def getModelNums(title):
    result = re.compile('[A-Z]*[0-9]{4,}').findall(title)

    if len(result):
        for i in range(len(result)):
            if result[i].find('2015') != -1 or result[i].find('2016') != -1 or result[i].find('2017') != -1 or result[
                i].find('2019') != -1:
                result[i] = ""

        return result

    return []

# 判断两个宝贝是否有共同款号
def hasCommonModelNum(t0, t1):
    modelNums0, modelNums1 = getModelNums(t0), getModelNums(t1)
    # print(t0, modelNums0, t1, modelNums1)
    if len(modelNums0) == 0 or len(modelNums1) == 0:
        return False

    for v0 in modelNums0:
        for v1 in modelNums1:
            if v0 != '' and v1 != '' and v0 == v1:
                return True

    return False

# 计算某款宝贝最早卖出的时间到最近卖出时间差值
def firDaySaleToLastDaySale(products):
    firstDay = products[0][-1]
    lastDay = products[0][-1]

    for d in products:
        if (d[-1] - firstDay).days < 0:
            firstDay = d[-1]

        if (d[-1] - lastDay).days > 0:
            lastDay = d[-1]

    return (lastDay - firstDay).days

def firstDaySale(products):
    firstDay = products[0][-1]

    for d in products:
        if (d[-1] - firstDay).days < 0:
            firstDay = d[-1]

    return firstDay

# 计算平均价格
def countAvgPrice(products):
    total = 0

    for p in products:
        total += int(p[2])

    return total / len(products)

# 判断两个价格是否都在同一个区间(0,300)/(300,600)/(600,1000),(1000,)
def samePriceGrade(p0, p1):
    p0 = int(p0)
    p1 = int(p1)

    if (p0 <= 300 and p1 <= 300) or (p0 > 300 and p0 <= 600 and p1 > 300 and p1 <= 600) or (
                            p0 > 600 and p0 <= 1000 and p1 > 600 and p1 <= 1000) or (p0 > 1000 and p1 > 1000):
        return True

    return False

# 获取同类宝贝
def getSimilarProducts(price, brand, category, spokeMan, title):
    result = {}

    for t in productsSummary:
        # 如果遍历到同款宝贝，则跳过，不予计算
        # if hasCommonModelNum(t, title):
        #     continue

        if brand != productsSummary[t]['brand']:
            continue

        if category != productsSummary[t]['category']:
            continue

        if spokeMan != productsSummary[t]['spkoeMan']:
            continue

        # 如果title中没有说明是女鞋，而t中说明是女鞋的情况下，过滤此条
        if title.find('女') == -1 and t.find('女') != -1:
            continue

        # 计算同类宝贝的平均价格和此款宝贝的价格,判断它们是否都在同一个区间
        avgPrice = countAvgPrice(productsSummary[t]['data'])
        if not samePriceGrade(price, avgPrice):
            continue

        result[t] = productsSummary[t].copy()

    return result

# 获取鞋码
def getSize(info):
    return info.split('：')[-1].split(' ')[0]

# 解析款号（去除中文字符）
def getModelInModelAndSize(modelAndSize):
    if len(modelAndSize) == 0:
        return ""
    # print(modelAndSize,modelAndSize.split(';')[0],modelAndSize.split(';')[0].split('：')[1])
    model = modelAndSize.split(';')[0].split('：')[1]

    if len(re.compile('[A-Za-z/0-9\-]*').findall(model)):
        model = re.compile('[A-Za-z/0-9\-]*').findall(model)[0]

    return model

# 为库存打分，综合考虑件数和尺码丰富度
def scoreForStock(stock):
    result = 0
    for size, count in stock.items():
        result += 1
        # if count <= 30:
        #     result += count * countSizeWeight(size)
        # else:
        #     result += 30 * countSizeWeight(size)
        result += count * countSizeWeight(size)

    return round(result, 2)

def countToalSale(sales):
    result = 0
    for size, count in sales.items():
        result += int(count)

    return result

def countTotalSaleByWeek(skuDataInWeek):
    """
    skuDataInWeek is array
    :param skuDataInWeek:
    :return:
    """

    result = {}

    for v in skuDataInWeek:
        for size, count in v["sale"].items():
            if not size in result:
                result[size] = 0
            result[size] += count

    return {k: v for k, v in sorted(result.items())}

def countTotalStockByWeek(totalSaleByWeek, nextWeekStartStock):
    result = {}

    for size, count in totalSaleByWeek.items():
        if not size in result:
            result[size] = 0

        if size in nextWeekStartStock["stock"]:
            result[size] = count + nextWeekStartStock["stock"][size]
        else:
            result[size] = count

    # 遍历出那些没有销售出去但是有剩余库存的尺码
    for size, count in nextWeekStartStock["stock"].items():
        if not size in result:
            result[size] = count

    return result

# 计算不同尺码的权重
info = {
    "43": 9437,
    "42.5": 8987,
    "42": 8338,
    "41": 6610,
    "44": 6367,
    "44.5": 2947,
    "40.5": 2644,
    "45": 2267,
    "40": 1639,
    "38": 1100,
    "36.5": 799,
    "37.5": 726,
    "39": 715,
    "46": 666,
    "38.5": 660,
    "36": 585,
    "37": 331,
    "35.5": 266,
    "45.5": 43,
    "47": 25,
    "47.5": 23,
    "41.5": 19,
    "46.5": 19,
    "39.5": 10,
    "48.5": 7,
    "48": 5,
    "43.5": 5,
    "35": 3
}

high_hot_sizes = ['43', '42.5', '42', '41', '44']
middle_hot_sizes = ['44.5', '40.5', '45', '40']

# total = 0
#
def countSizeWeight(size):
    if size in info:
        return round(info[size] / 20000, 2)
    else:
        return 0.01

# 获得某个尺码的热销程度
def sizeHotSale(size):
    if info[size] > 8000:
        return 3
    elif info[size] > 1000:
        return 2
    else:
        return 1

# 所有历史销售数据都为空
def emptyHistorySale(historySkuDataInWeeks):
    # if len(historySkuDataInWeeks) < 2:
    #     return False

    for item in historySkuDataInWeeks:
        if len(item['sale']):
            return False

    return True

# 根据尺寸获取历史销售数据数组
def saleArrBySize(historySkuDataInWeeks, size_):
    arr = []

    for week in historySkuDataInWeeks:
        for size, count in week['sale'].items():
            if size == size_:
                arr.append(count)

    return arr

# 剔除数组中的异常值
def eliminateAbnormalValue(arr):
    if len(arr) == 0:
        return []

    mean = sum(arr) / len(arr)

    result = []

    if len(arr) == 1:
        return arr
    elif len(arr) == 2:
        if min(arr) == 1 and max(arr) >= 8:
            result.append(min(arr))
        elif max(arr) / min(arr) > 4:
            result.append(min(arr))
        else:
            return arr
    else:
        for v in arr:
            if v > mean * 2:
                continue

            result.append(v)

    return result

# math.sqrt(sum([(v - (sum(s) / len(s))) ** 2 for v in s]) / len(s))
def increasingList(arr):
    for i in range(len(arr) - 1):
        if arr[i + 1] < arr[i]:
            return False
    return True

# 改进之后的sku销售预测
def predictSkuSaleCount2(historySkuDataInWeeks, predictedSkuStock, actualSkuSale, model, sizeSale):
    result = {}

    lastWeekData = historySkuDataInWeeks[-1]

    # 如果历史上一直销量为零，那么预测后面的周期也为零
    if emptyHistorySale(historySkuDataInWeeks):
        for size in predictedSkuStock:
            result[size] = 0

        return result

    # 只有一周的历史销售数据
    if len(historySkuDataInWeeks) == 1:
        for size, count in predictedSkuStock.items():
            if size in lastWeekData['sale'] and lastWeekData['sale'][size] == 1:
                result[size] = 1

    # 有一周以上的历史销售数据
    if len(historySkuDataInWeeks) > 1:
        for size, count in predictedSkuStock.items():
            # 获取历史销售数据并剔除一些极端值
            # sales = eliminateAbnormalValue(saleArrBySize(historySkuDataInWeeks, size))
            sales = saleArrBySize(historySkuDataInWeeks, size)
            # 如果有一个以上的历史数据,暂时简单地取一个均值
            if len(sales) > 1:
                if size in sizeSale and sizeSale[size] > 10:
                    predictedSale = math.ceil(sum(sales) / len(sales))
                else:
                    predictedSale = math.ceil(sum(sales) / len(sales))

                result[size] = predictedSale

            # 如果只有一个周期有过销量
            elif len(sales) == 1:
                # 如果这个尺码在历史上总销量低于3双，那么预测无销量
                if sizeSale[size] < 3:
                    result[size] = 0
                # 如果新库存比这个有销量的周期的库存大那么预测能持平
                else:
                    result[size] = sales[0]

            # 如果没有历史周期数据可供参考
            else:
                if size in sizeSale and sizeSale[size] > 10:
                    result[size] = 1

            if size in result:
                result[size] = result[size] if result[size] >= 0 else 0
                result[size] = result[size] if result[size] <= count else count

    return result
